import amara
from amara import binderytools
import sys
import string

import MySQLdb

def clean(s):
    if s != None:
        return s.replace("'", "\\'").strip()
    else:
        return s

def connect_to_db(db_hostname, db_name, db_username, db_passwd):
    conn = MySQLdb.connect(db_hostname, db_username, db_passwd)
    cursor = conn.cursor()
    cursor.execute("use %s" % db_name)
    cursor.execute("SET NAMES utf8;SET CHARACTER SET utf8;SET character_set_connection=utf8;")

    return conn

def add_citeinfo(citeinfo, conn):
    if citeinfo.xml_child_elements.has_key('origin'):
        origin = "'%s'" % citeinfo.origin
    else:
        origin = 'NULL'

    if citeinfo.xml_child_elements.has_key('pubdate'):
        pubdate = "'%s'" % citeinfo.pubdate
    else:
        pubdate = 'NULL'

    if citeinfo.xml_child_elements.has_key('pubtime'):
        pubtime = "'%s'" % citeinfo.pubtime
    else:
        pubtime = 'NULL'

    if citeinfo.xml_child_elements.has_key('title'):
        title = "'%s'" % citeinfo.title
    else:
        title = 'NULL'

    if citeinfo.xml_child_elements.has_key('edition'):
        edition = "'%s'" % citeinfo.edition
    else:
        edition = 'NULL'

    if citeinfo.xml_child_elements.has_key('geoform'):
        geoform = "'%s'" % citeinfo.geoform
    else:
        geoform = 'NULL'

    if citeinfo.xml_child_elements.has_key('serinfo'):
        sername = "'%s'" % citeinfo.serinfo.sername
        issue = "'%s'" % citeinfo.serinfo.issue
    else:
        sername = 'NULL'
        issue = 'NULL'

    if citeinfo.xml_child_elements.has_key('pubinfo'):
        pubplace = "'%s'" % citeinfo.pubinfo.pubplace
        publish = "'%s'" % citeinfo.pubinfo.publish
    else:
        pubplace = 'NULL'
        publish = 'NULL'

    if citeinfo.xml_child_elements.has_key('othercit'):
        othercit = "'%s'" % citeinfo.othercit
    else:
        othercit = 'NULL'

    if citeinfo.xml_child_elements.has_key('onlink'):
        onlink = "'%s'" % citeinfo.onlink
    else:
        onlink = 'NULL'

    if citeinfo.xml_child_elements.has_key('lworkcit'):
        lworkcit = "'%s'" % citeinfo.lworkcit
    else:
        lworkcit = 'NULL'

    if citeinfo.xml_child_elements.has_key('scale'):
        scale = "'%s'" % citeinfo.scale
    else:
        scale = 'NULL'

    query = """
    INSERT INTO citeinfo VALUES(NULL, %s, %s, %s, %s,
                                %s, %s, %s, %s, %s,
                                %s, %s, %s, %s, %s)
    """ % (origin, pubdate, pubtime, title, edition, geoform,
           sername, issue, pubplace, publish, othercit, onlink,
           lworkcit, scale)

    cursor = conn.cursor()
    cursor.execute(query)

    return cursor.lastrowid

def add_spdom(spdom, conn):
    if spdom.xml_child_elements.has_key('bounding'):
        westbc = "'%s'" % spdom.bounding.westbc
        eastbc = "'%s'" % spdom.bounding.eastbc
        northbc = "'%s'" % spdom.bounding.northbc
        southbc = "'%s'" % spdom.bounding.southbc
    else:
        westbc = 'NULL'
        eastbc = 'NULL'
        northbc = 'NULL'
        southbc = 'NULL'

    query = """
    INSERT INTO spdom VALUES(NULL, %s, %s, %s, %s)
    """ % (westbc, eastbc, northbc, southbc)

    cursor = conn.cursor()
    cursor.execute(query)

    return cursor.lastrowid
    
def add_idinfo(idinfo, conn, doc):
    citeinfo_doc = doc.xml_xpath(u"//citeinfo")
    citeinfoID = add_citeinfo(citeinfo_doc[0], conn)
    
    spdom_doc = doc.xml_xpath(u"//spdom")
    spdomID = add_spdom(spdom_doc[0], conn)

    if idinfo.xml_child_elements.has_key('descript'):
        abstract = "'%s'" % clean(idinfo.descript.abstract)
        purpose = "'%s'" % idinfo.descript.purpose
        supplinf = "'%s'" % idinfo.descript.supplinf
    else:
        abstract = 'NULL'
        purpose = 'NULL'
        supplinf = 'NULL'

    if idinfo.timeperd.timeinfo.xml_child_elements.has_key('rngdates'):
        begdate = "'%s'" % idinfo.timeperd.timeinfo.rngdates.begdate
        enddate = "'%s'" % idinfo.timeperd.timeinfo.rngdates.enddate
    else:
        begdate = 'NULL'
        enddate = 'NULL'

    current = "'%s'" % idinfo.timeperd.current

    progress = "'%s'" % idinfo.status.progress
    update = "'%s'" % idinfo.status.update

    language = 'NULL'
    accconst = "'%s'" % idinfo.accconst
    useconst = "'%s'" % idinfo.useconst
    datacred = 'NULL'
    native = 'NULL'
    ptcontact = 'NULL'
    crossref = 'NULL'
    browsen = 'NULL'
    browsed = 'NULL'
    browset = 'NULL'
    secsys = 'NULL'
    secclass = 'NULL'
    sechandl = 'NULL'

    themekey_list = []
    if idinfo.keywords.xml_child_elements.has_key('theme'):
        for keyword in doc.xml_xpath(u'//themekey'):
            print keyword
            themekey_list.append(str(keyword))

        themekey = "'%s'" % string.join(themekey_list, ';')
        themekt = "'%s'" % idinfo.keywords.theme.themekt
    placekey_list = []
    if idinfo.keywords.xml_child_elements.has_key('place'):
        for keyword in doc.xml_xpath(u'//placekey'):
            placekey_list.append(str(keyword))

        placekey = "'%s'" % string.join(placekey_list, ';')
        placekt = "'%s'" % idinfo.keywords.place.placekt
        
    query = """
    INSERT INTO idinfo VALUES(NULL, %s, %s, %s, %s,
                              %s, %s, %s, %s, %s, %s,
                              %s, %s, %s, %s, %s, %s,
                              %s, %s, %s, %s, %s, %s,
                              %s, %s, %s, %s, %s)
    """ % (citeinfoID, spdomID, abstract, purpose, supplinf,
           language, accconst, useconst, datacred, native,
           ptcontact, crossref, browsen, browsed, browset,
           secsys, secclass, sechandl, begdate, enddate, current,
           progress, update, themekt, themekey, placekt, placekey)

    cursor = conn.cursor()
    cursor.execute(query)

    return cursor.lastrowid

def add_distinfo(distinfo, conn, doc):
    cntinfo_doc = doc.xml_xpath(u"//cntinfo")
    cntinfoID = 'NULL' #add_cntinfo(cntinfo_doc[0], conn)
    
    resdesc = 'NULL'
    disliab = "'None'"
    custom = "''"
    techpreq = "''"

    if distinfo.xml_child_elements.has_key('stdorder'):
        formname = "'%s'" % distinfo.stdorder.digform.digtinfo.formname
        formvern = "'%s'" % distinfo.stdorder.digform.digtinfo.formvern
    else:
        formname = "''"
        formvern = "''"

    networkr = "'%s'" % distinfo.stdorder.digform.digtopt.onlinopt.networka.networkr

    query = """
    INSERT INTO distinfo VALUES(NULL, %s, %s, %s, %s, %s, %s, %s, %s)
    """ % (cntinfoID, resdesc, disliab, custom, techpreq, formname, formvern, networkr)

    cursor = conn.cursor()
    cursor.execute(query)

    return cursor.lastrowid

def add_metainfo(metainfo, conn):
    cntinfoID = 'NULL'
    metd = metainfo.metd
    metrd = 'NULL'
    metfrd = 'NULL'
    mettc = 'NULL'
    metstdn = 'NULL'
    metstdv = 'NULL'
    metac = 'NULL'
    metuc = 'NULL'

    query = """
    INSERT INTO metainfo VALUES(NULL, %s, %s, %s, %s, %s, %s, %s, %s, %s)
    """ % (cntinfoID, metd, metrd, metfrd, mettc, metstdn, metstdv, metac, metuc)

    cursor = conn.cursor()
    cursor.execute(query)
    
    return cursor.lastrowid

def add_dataqual(dataqual, conn):
    #dataqualID = 'NULL'

    if dataqual.lineage.xml_child_elements.has_key('srcinfo'):
        if str(dataqual.lineage.srcinfo.srcscale) != '':
            srcscale = dataqual.lineage.srcinfo.srcscale
        else:
            srcscale = 'NULL'
    else:
        srcscale = 'NULL'   

    query = """
    INSERT INTO dataqual VALUES(NULL, %s);
    """ % srcscale
    print query

    cursor = conn.cursor()
    cursor.execute(query)
    
    return cursor.lastrowid

def add_spdoinfo(spdoinfo, conn):
    spdoinfoID = 'NULL'

    if spdoinfo.xml_child_elements.has_key('rastinfo'):
        rowcount = spdoinfo.rastinfo.rowcount
        colcount = spdoinfo.rastinfo.colcount
    else:
        rowcount = 'NULL'
        colcount = 'NULL'

    query = """
    INSERT INTO spdoinfo VALUES(NULL, %s, %s)
    """ % (rowcount, colcount)

    cursor = conn.cursor()
    cursor.execute(query)

    return cursor.lastrowid    
    
def fgdc_to_db(doc, db_hostname, db_name, db_username, db_passwd):
    conn = connect_to_db(db_hostname, db_name, db_username, db_passwd)

    idinfo = doc.xml_xpath(u"//idinfo")
    idinfoID = add_idinfo(idinfo[0], conn, doc)
    distinfo = doc.xml_xpath(u"//distinfo")
    distinfoID = add_distinfo(distinfo[0], conn, doc)
    metainfo = doc.xml_xpath(u"//metainfo")
    metainfoID = add_metainfo(metainfo[0], conn)

    if doc.metadata.xml_child_elements.has_key('dataqual'):
        dataqual = doc.xml_xpath(u'//dataqual')
        dataqualID = add_dataqual(dataqual[0], conn)
    else:
        dataqualID = 'NULL'

    if doc.metadata.xml_child_elements.has_key('spdoinfo'):
        spdoinfo = doc.xml_xpath(u'//spdoinfo')
        spdoinfoID = add_spdoinfo(spdoinfo[0], conn)
    else:
        spdoinfoID = 'NULL'
        
    sprefId = 'NULL'
    eainfoID = 'NULL'
    cntinfoID = 'NULL'
    
    query = """
    INSERT INTO fgdcroot VALUES(NULL, %s, %s, %s, %s, %s, %s, %s, %s)
    """ % (idinfoID, dataqualID, spdoinfoID, sprefId, eainfoID, 
           distinfoID, metainfoID, cntinfoID)

    cursor = conn.cursor()
    cursor.execute(query)
    conn.commit()
        
def insert_kml_url(url, fgdc):
    stdorder = fgdc.xml_create_element(u"stdorder")
    digform = fgdc.xml_create_element(u"digform")
    digtinfo = fgdc.xml_create_element(u"digtinfo")
    formname = fgdc.xml_create_element(u"formname", content=u"KML")
    formvern = fgdc.xml_create_element(u"formvern", content=u"2.1")

    digtopt = fgdc.xml_create_element(u"digtopt")
    onlinopt = fgdc.xml_create_element(u"onlinopt")
    networka = fgdc.xml_create_element(u"networka")
    networkr = fgdc.xml_create_element(u"networkr", content=unicode(url))

    if not fgdc.metadata.xml_child_elements.has_key('distinfo'):
        distinfo = fgdc.xml_create_element(u'distinfo')
        fgdc.metadata.xml_append(distinfo)
        
    fgdc.metadata.distinfo.xml_append(stdorder)
    fgdc.metadata.distinfo.stdorder.xml_append(digform)
    fgdc.metadata.distinfo.stdorder.digform.xml_append(digtinfo)
    fgdc.metadata.distinfo.stdorder.digform.digtinfo.xml_append(formname)
    fgdc.metadata.distinfo.stdorder.digform.digtinfo.xml_append(formvern)
    fgdc.metadata.distinfo.stdorder.digform.xml_append(digtopt)
    fgdc.metadata.distinfo.stdorder.digform.digtopt.xml_append(onlinopt)
    fgdc.metadata.distinfo.stdorder.digform.digtopt.onlinopt.xml_append(networka)
    fgdc.metadata.distinfo.stdorder.digform.digtopt.onlinopt.networka.xml_append(networkr)

def open_fgdc_xml(filename):
    rules = [binderytools.simple_string_element_rule(u'abstract')]
    doc = amara.parse(open(filename, 'r'), rules=rules)
    return doc

if __name__ == '__main__':
    doc = open_fgdc_xml('test.xml')
    insert_kml_url('http://test.org/', doc)
    fgdc_to_db(doc, 'imlsmap.lib.uconn.edu', 'MLMMS_test', 'mkorman', 'password')    
